package main

import (
	"database/sql/driver"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql" // 驱动
	"github.com/jmoiron/sqlx"
	"strings"
)

var db *sqlx.DB

type user struct {
	Id int `db:"id"` // tag 为数据库中对应的字段
	Username string `db:"username"`
	Password string `db:"password"`
}

func (u user) Value() (driver.Value, error) {
	return []interface{}{u.Username, u.Password}, nil
}

func initDB() (err error) {
	dsn := "root:root@tcp(192.168.0.7:3309)/golang_db?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", dsn) // open + ping
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

// 查询单条数据示例
func queryRowDemo() {
	println("查询单条数据示例>>>>>>>>>>>>>>>>>")
	sqlStr := "select id, username, password from users where id=?"
	var u user
	err := db.Get(&u, sqlStr, 9)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d 用户名:%s 密码:%s\n", u.Id, u.Username, u.Password)
}

// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, username, password from users where id > ?"
	var users []user
	err := db.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	for _, yh := range users {
		fmt.Printf("ID:%d  用户名：%s, 密码： %s\n", yh.Id, yh.Username, yh.Password)
	}
}
// 插入数据
func insertRowDemo() {
	sqlStr := "insert into users(username, password) values (?,?)"
	ret, err := db.Exec(sqlStr, "风吹裤裆蛋蛋凉", "mmmmmmmmmm")
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}
// 更新数据
func updateRowDemo() {
	sqlStr := "update users set password=? where id = ?"
	ret, err := db.Exec(sqlStr, "00000000000000000s", 6)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from users where id = ?"
	ret, err := db.Exec(sqlStr, 9)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

// NamedExec
func insertUserDemo()(err error){
	sqlStr := "INSERT INTO users (username,password) VALUES (:username,:password)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"username": "派大星",
			"password": "110",
		})
	u := user{
		Username: "章鱼哥",
		Password: "shudi",
	}
	_, err = db.NamedExec(sqlStr, u)

	return
}

// NamedQuery
func namedQuery(){
	// 如果使用 select * ,结构体映射的字段必须和数据库中字段数量吻合！
	sqlStr := "SELECT id, username, password FROM users WHERE username=:username"
	// 使用map做命名查询
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"username": "派大星"})
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
		// 放到结构体中
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	u := user{
		Username: "海绵宝宝",
	}
	// 使用结构体命名查询，根据结构体字段的 db tag进行映射
	rows, err = db.NamedQuery(sqlStr, u)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
}
func transactionDemo2()(err error) {
	tx, err := db.Beginx() // 开启事务
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}
	// 收尾判断
	defer func() {
		// 捕获
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // re-throw panic after Rollback
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			fmt.Println("commit")
		}
	}()

	sqlStr1 := "Update users set password='20888888888888' where id=?"

	rs, err := tx.Exec(sqlStr1, 14)
	if err!= nil{
		return err
	}
	n, err := rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	sqlStr2 := "Update users set password='ksssssssssssscsscscs' where id=?"
	rs, err = tx.Exec(sqlStr2, 18)
	if err!=nil{
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err
}

// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers2(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO users (username, password) VALUES (?), (?), (?)",
		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := db.Exec(query, args...)
	return err
}


// BatchInsertUsers3 使用NamedExec实现批量插入
func BatchInsertUsers3(users []*user) error {
	_, err := db.NamedExec("INSERT INTO users (username, password) VALUES (:username, :password)", users)
	return err
}

// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int)(users []user, err error) {
	// 动态填充id
	query, args, err := sqlx.In("SELECT id, username, password FROM users WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}
// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int)(users []user, err error){
	// 动态填充id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT id, username, password FROM users WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}

func main() {
	err := initDB()
	if err != nil {
		println("连接失败")
	} else {
		println("连接成功！")
	}
	//queryRowDemo()
	//insertRowDemo()
	//updateRowDemo()
	//queryMultiRowDemo()
	//deleteRowDemo()
	//_ = insertUserDemo()
	//queryMultiRowDemo()
	//namedQuery()
	//_ = insertUserDemo()
	//_ = transactionDemo2()
	//users := []interface{}{user{Username: "珊迪", Password: "1111111"}, user{Username: "蟹老板", Password: "钱钱钱"}, user{Username: "痞老板", Password: "蟹黄堡秘密配方"}}
	//users2 := []*user{&user{Username: "泡芙老师", Password: "1111111"}, &user{Username: "小蜗", Password: "钱钱钱"}, &user{Username: "虾霸", Password: "蟹黄堡秘密配方"}}
	//_ = BatchInsertUsers2(users)
	//_ = BatchInsertUsers3(users2)
	users, _ := QueryByIDs([]int{11, 2, 5})
	fmt.Println(users)
	users, _ = QueryAndOrderByIDs([]int{11, 2, 5})
	fmt.Println(users)




}
